#include <stdlib.h>
#include <string.h>
#include "stdio.h"
#include "sqlite3.h"
#include "database.h"
#define USER_COUNT 15
typedef struct{
    char name[20];
    char password[20];
    int age;
    char sex[20];
    char phone[11];
    char address[50];
    int user_id;
    int is_vip;
}User;


// 修改用户
int create_user_update(User *u){
    char sql[256];
    char *errmsg=NULL;
    sprintf(sql,"update create_user set name='%s',password='%s',age=%d,sex='%s',phone='%s',address='%s' where user_id=%d;",
            u->name,u->password,u->age,u->sex,u->phone,u->address,u->user_id);
    int rc= sqlite3_exec(db,sql,NULL,NULL,&errmsg);
    if(rc!=SQLITE_OK){
        fprintf(stderr,"create_user_update:修改数据失败：%s\n",errmsg);
        sqlite3_free(errmsg);
        return -1;
    }
    printf("create_user_update：修改数据成功！\n");
    return 0;
}
// 增加用户
int create_user_insert(User *new_user){
    const char *sql="insert into create_user(name,password,age,sex,phone,address)VALUES(?,?,?,?,?,?);";
    sqlite3_stmt *stmt;
    int rc= sqlite3_prepare_v2(db,sql,-1,&stmt,NULL);
    if(rc!=SQLITE_OK){
        fprintf(stderr,"create_user_insert:预编译失败：%s\n", sqlite3_errmsg(db));
        return -1;
    }
    rc = sqlite3_exec(db, "BEGIN TRANSACTION;", NULL, NULL, NULL);
    if (rc != SQLITE_OK) {
        fprintf(stderr, "开启事务失败：%s\n", sqlite3_errmsg(db));
        sqlite3_finalize(stmt);
        return -1;
    }
    rc = sqlite3_bind_text(stmt, 1, new_user->name, -1, SQLITE_TRANSIENT);
    if (rc != SQLITE_OK) { fprintf(stderr, "绑定name失败：%s\n", sqlite3_errmsg(db)); goto FAIL; }
    rc = sqlite3_bind_text(stmt, 2, new_user->password, -1, SQLITE_TRANSIENT);
    if (rc != SQLITE_OK) { fprintf(stderr, "绑定password失败：%s\n", sqlite3_errmsg(db)); goto FAIL; }
    rc = sqlite3_bind_int(stmt, 3, new_user->age);
    if (rc != SQLITE_OK) { fprintf(stderr, "绑定age失败：%s\n", sqlite3_errmsg(db)); goto FAIL; }
    rc = sqlite3_bind_text(stmt, 4, new_user->sex, -1, SQLITE_TRANSIENT);
    if (rc != SQLITE_OK) { fprintf(stderr, "绑定sex失败：%s\n", sqlite3_errmsg(db)); goto FAIL; }
    rc = sqlite3_bind_text(stmt, 5, new_user->phone, -1, SQLITE_TRANSIENT);
    if (rc != SQLITE_OK) { fprintf(stderr, "绑定phone失败：%s\n", sqlite3_errmsg(db)); goto FAIL; }
    rc = sqlite3_bind_text(stmt, 6, new_user->address, -1, SQLITE_TRANSIENT);
    if (rc != SQLITE_OK) { fprintf(stderr, "绑定address失败：%s\n", sqlite3_errmsg(db)); goto FAIL; }
    // 执行插入
    rc = sqlite3_step(stmt);
    if (rc != SQLITE_DONE) {
        fprintf(stderr, "create_user_insert：插入失败：%s\n", sqlite3_errmsg(db));
        goto FAIL;
    }
    // 插入成功，提交事务
    rc = sqlite3_exec(db, "COMMIT;", NULL, NULL, NULL);
    if (rc == SQLITE_OK) {
        printf("create_user_insert：用户【%s】插入成功！\n", new_user->name);
    } else {
        fprintf(stderr, "提交事务失败：%s\n", sqlite3_errmsg(db));
        goto FAIL;
    }
    sqlite3_finalize(stmt);
    return 0;
    FAIL:
    // 插入失败，回滚事务
    sqlite3_exec(db, "ROLLBACK;", NULL, NULL, NULL);
    sqlite3_finalize(stmt);
    return -1;
}
// 查询用户信息
int create_user_query(char *name){
    const char *sql="select user_id, name, password, age, sex, phone, address from create_user where name=?;";
    sqlite3_stmt *stmt;
    int rc= sqlite3_prepare_v2(db,sql,-1,&stmt,NULL);
    if(rc!=SQLITE_OK){
        fprintf(stderr,"create_user_query：预编译失败：%s\n", sqlite3_errmsg(db));
        return -1;
    }

    sqlite3_bind_text(stmt,1,name,-1,SQLITE_TRANSIENT);
    printf("查询name='%s'的用户信息：\n",name);
    int result_count=0;

    while(sqlite3_step(stmt)==SQLITE_ROW){
        result_count++;
        int user_id= sqlite3_column_int(stmt,0);
        const char *user_name=(const char *) sqlite3_column_text(stmt,1);
        const char *password=(const char *) sqlite3_column_text(stmt,2);
        int age= sqlite3_column_int(stmt,3);
        const char *sex = (const char*)sqlite3_column_text(stmt,4);
        const char *phone = (const char*)sqlite3_column_text(stmt,5);
        const char *address = (const char*)sqlite3_column_text(stmt,6);

        printf("user_id:%d\n",user_id);
        printf("user_name:%s\n",user_name);
        printf("password=%s\n",password);
        printf("age=%d\n",age);
        printf("sex=%s\n",sex);
        printf("phone=%s\n",phone);
        printf("address=%s\n",address);
        printf("--------------------------------------\n");
    }

    if (result_count == 0) {
        printf("create_user_query：未查询到 name='%s' 的用户\n", name);
    }

    sqlite3_finalize(stmt);
    return 0;
}
// 删除用户信息
int create_user_delete(int user_id){
    char sql[256];
    sprintf(sql,"delete from create_user where user_id=%d;",user_id);
    char *errmsg=NULL;
    int rc= sqlite3_exec(db,sql,NULL,NULL,&errmsg);
    if(rc!=SQLITE_OK){
        fprintf(stderr,"create_user_delete：删除数据失败！错误信息：%s\n",errmsg);
        sqlite3_free(errmsg);
        return -1;
    }
    printf("create_user_delete：user_id=%d的用户删除成功！\n",user_id);
    return 0;
}
